﻿// ===============================================================================
// 文件名：系统_用户维护
// 作用：对系统用户进行维护
// 作者：bingo
// 时间：2011-6-01
// ===============================================================================
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Configuration;
using System.Data.SqlClient;
using SqlCon;

namespace yfxt
{
    public partial class xt_yhwh : Form
    {
        string comStr;
        DataTable dt = new DataTable();
        int current_position = 0;

        public xt_yhwh()
        {
            InitializeComponent();

        }

        #region   获取gy_ygdm用户表记录，将需要的信息添加到datagridview1中
        private void xt_yhwh_Load_1(object sender, EventArgs e)
        {

            try
            {
                comStr = "select ygdm,ygxm,ygbh,ksmc,ygjb from gy_ygdm a,gy_ksdm b where a.ksdm=b.ksdm  order by YGDM";
                dt.Load(SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, comStr, null));
                //sqlcon = new SqlCon.SqlCon();
                //comStr = "select 编号,姓名,所在科室,职位,角色 from ry where 所在科室='" + ks + "'";
                //con = sqlcon.GetCon;
                //ds = sqlcon.GetDs();
                //dt_renyuan = ds.Tables.Add();
                //da = sqlcon.GetSqlda();
                //da.SelectCommand = sqlcon.GetCom();
                //da.SelectCommand.CommandText = comStr;
                //da.SelectCommand.Connection = con;
                //con.Open();
                //da.Fill(dt_renyuan);
                //con.Close();
                dataGridView1.Columns[0].DataPropertyName = dt.Columns[0].ToString();
                dataGridView1.Columns[1].DataPropertyName = dt.Columns[1].ToString();
                dataGridView1.Columns[2].DataPropertyName = dt.Columns[2].ToString();
                dataGridView1.Columns[3].DataPropertyName = dt.Columns[3].ToString();
                dataGridView1.Columns[4].DataPropertyName = dt.Columns[4].ToString();
                dataGridView1.DataSource = dt;
                //DataGridViewColumn dc = new DataGridViewColumn();
                //dc.SortMode = DataGridViewColumnSortMode.NotSortable;
                textBox1.Enabled = false;

            }
            catch (Exception ex)
            { MessageBox.Show(ex.Message.ToString()); }
            comboBox1.Items.Add("普通员工");
            comboBox1.Items.Add("科室主任");

        }
        #endregion

        #region  退出按钮
        private void button5_Click(object sender, EventArgs e)
        {
            //try
            //{
            //    DataTable dt = new DataTable();
            //    string ksmc = textBox4.Text.ToString().Trim();
            //    string ksdm;
            //    string sqlcmd = "select ksdm from gy_ksdm where ksmc=@ksmc";
            //    SqlParameter[] apara = new SqlParameter[]{
            //      new SqlParameter("@ksmc",ksmc)  
            //    };
            //    dt.Load(SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sqlcmd, apara));
            //    ksdm = dt.Rows[0]["ksdm"].ToString();
            //    MessageBox.Show(ksdm);
            //}
            //catch (Exception ex)
            //{
            //    MessageBox.Show(ex.ToString());

            //}

            Close();
            }
        #endregion

        #region 新增员工记录
        private void button1_Click(object sender, EventArgs e)
        {
            textBox1.Enabled = true;
            textBox1.Text = "";
            textBox2.Text = "";
            textBox3.Text = "";
            textBox4.Text = "";
            textBox5.Text = "";
        }
        #endregion

        #region 删除员工工号
        private void button3_Click(object sender, EventArgs e)
        {
            DataTable dt=new DataTable();
            try 
            {
                //string record;
                int spk;
                string ygdm = textBox1.Text.ToString().Trim();
                //string ygbh = textBox3.Text.ToString().Trim();
                string comstr;
                comstr = "delete from gy_ygdm where ygdm=@ygdm";  //删除记录语句
                //res = "select * from gy_gydm whre ygdm=@ygbh";  //判断是否存在工号语句

                //数组参数
                SqlParameter[] spara1 = new SqlParameter[]
                {
                    new SqlParameter("@ygdm",ygdm)
                };
                //SqlParameter[] spara2 = new SqlParameter[]
                //{
                //    new SqlParameter("@ygbh",ygbh)
                //};
                
                if(ygdm==null)
                {                 
                    MessageBox.Show("用户编号为空，请输入员工编号");
                    return;
                }
                else
                {
                    //spk=SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, res, spara2);
                    ////record = dt.Rows[0]["ygdm"].ToString();
                    //if(spk==0)
                    //{
                    //    MessageBox.Show("不存在该员工编号或记录，请输入正确的员工编号");
                    //    return;
                    //}
                    //else
                    //{
                        spk=SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction,CommandType.Text,comstr,spara1);
                        if (spk == 0)
                        {
                            MessageBox.Show("删除失败，请重新尝试");
                        }
                        else
                        {
                            MessageBox.Show("删除成功");
                        //}
                    }
                }
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }
        #endregion

        #region  保存新增用户
        private void button4_Click(object sender, EventArgs e)
        {
            SqlDataAdapter da = new SqlDataAdapter();
           if (textBox1.Text.CompareTo("") == 0 || textBox2.Text.CompareTo("") == 0 || textBox3.Text.CompareTo("") == 0 || textBox4.Text.CompareTo("") == 0 || textBox5.Text.CompareTo("") == 0)
            {
                MessageBox.Show("请填写完整用户信息");
            }

            else
            {
                try
                {
                    //取出科室代码
                    DataTable dt = new DataTable();
                    string ksmc = textBox4.Text.ToString().Trim();
                    string ksdm;
                    int res2;
                    string sqlcmd = "select ksdm from gy_ksdm where ksmc=@ksmc";
                    string sqlcmd2="select * from gy_ygdm where ygdm='"+textBox1.Text.ToString().Trim()+"' or ygbh='"+textBox3.Text.ToString().Trim()+"'" ;
                    SqlParameter[] apara = new SqlParameter[]
                    {
                        new SqlParameter("@ksmc",ksmc)  
                    };
                    dt.Load(SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sqlcmd, apara));
                    ksdm = dt.Rows[0]["ksdm"].ToString();
                    res2=SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction,CommandType.Text,sqlcmd2,null);
                    if(res2!=0)
                    {
                        MessageBox.Show("用户代码或员工编号重复,重新输入");
                    }
                    else
	                {
                        string inser = "insert into gy_ygdm values('" + textBox1.Text.ToString() + "','" + ksdm + "','" + textBox2.Text.ToString() + "', null,null,null,'"+comboBox1.Text.ToString()+"',null,null,null,null,null,null,null,null,'" + textBox3.Text.ToString() + "','" + textBox5.Text.ToString() + "',null,null,null,null,null,null,null)";
                        int res1 = SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, inser, null);
                        if (res1 != 0)
                        {
                            MessageBox.Show("保存成功");
                            textBox1.Enabled = false;
                        }     
	                }
                    
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
            }
        }
        #endregion

        #region  textboxchange事件，如果存在则搜索出科室名称表
        private void textBox4_TextChanged(object sender, EventArgs e)
        {          
            string pydm = textBox4.Text.Trim();
            string sqlstr = "select ksmc as '科室名称' from gy_ksdm where pydm like '"+pydm+"%'";
            DataTable dtload = new DataTable();
            dataGridView2.Visible = true;
            dtload.Load(SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction,CommandType.Text,sqlstr,null));
            dataGridView2.DataSource = dtload;
        }
        #endregion

        #region 鼠标上下移动选中科室名称
        private void textBox4_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Escape)
            {
                textBox4.Focus();
                dataGridView2.Visible = false;
            }

            if (e.KeyCode == Keys.Down)
            {
                dataGridView2.Focus();
                dataGridView2.CurrentRow.Selected = true;
            }

            if (e.KeyCode ==Keys.Enter)
            {
                int z = dataGridView2.CurrentRow.Index;
                this.textBox4.Text = this.dataGridView2.Rows[z].Cells[0].Value.ToString();
                this.dataGridView2.Visible = false;
            }
        }
        #endregion

        #region 单击将选中表格中的值添加到科室名称中
        private void dataGridView2_Click(object sender, EventArgs e)
        {
            int z = dataGridView2.CurrentRow.Index;
            this.textBox4.Text = this.dataGridView2.Rows[z].Cells[0].Value.ToString();
            this.dataGridView2.Visible = false;
        }
        #endregion

        #region 修改提交
        private void button2_Click(object sender, EventArgs e)
        {
            DataTable dt = new DataTable();
            string sqlstr1,sqlstr2,ksdm,ksmc;
            int res;
           //取科室代码
            ksmc = textBox4.Text.ToString().Trim();
            sqlstr1 = "select ksdm from gy_ksdm where ksmc=@ksmc";
            SqlParameter[] apara = new SqlParameter[]
                    {
                        new SqlParameter("@ksmc",ksmc)  
                    };
            dt.Load(SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sqlstr1, apara));
            ksdm = dt.Rows[0]["ksdm"].ToString();
            //
            //
            sqlstr2 = "update gy_ygdm set ygxm='" + textBox2.Text.ToString().Trim() + "',ygbh='" + textBox3.Text.ToString().Trim() + "',ksdm='" + ksdm + "',ygmm='" + textBox5.Text.ToString().Trim() + "',ygjb='" + comboBox1.Text.ToString().Trim() + "' where ygdm='" + textBox1.Text.ToString().Trim() + "'";
            if (textBox2.Text.CompareTo("") == 0 || textBox3.Text.CompareTo("") == 0 || textBox4.Text.CompareTo("") == 0 || textBox5.Text.CompareTo("") == 0)
            {
                MessageBox.Show("请填写完整信息");
            }
            else
            {
                res = SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sqlstr2, null);
                if(res!=0)
                {
                    MessageBox.Show("修改成功");
                    textBox1.Enabled = false;
                }
            }
        }
        #endregion

        #region  点击记录行，将信息添加到右侧textbox中
        private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            current_position = dataGridView1.CurrentRow.Index;//.CurrentCell.RowIndex;
            textBox1.Text = dataGridView1.Rows[current_position].Cells["Column1"].Value.ToString();
            textBox2.Text = dataGridView1.Rows[current_position].Cells["Column2"].Value.ToString();
            textBox3.Text = dataGridView1.Rows[current_position].Cells["Column3"].Value.ToString();
            textBox4.Text = dataGridView1.Rows[current_position].Cells["Column4"].Value.ToString();
            comboBox1.Text = dataGridView1.Rows[current_position].Cells["Column5"].Value.ToString();
            textBox5.Text = "";
            dataGridView2.Visible = false;
        }
        #endregion

        private void textBox6_KeyUp(object sender, KeyEventArgs e)
        {
            dt.Clear();
            try
            {
                comStr = "select ygdm,ygxm,ygbh,ksmc,ygjb from gy_ygdm a,gy_ksdm b where a.ksdm=b.ksdm and ygbh like '"+textBox6.Text.Trim()+"%' order by YGDM";
                dt.Load(SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, comStr, null));
                dataGridView1.Columns[0].DataPropertyName = dt.Columns[0].ToString();
                dataGridView1.Columns[1].DataPropertyName = dt.Columns[1].ToString();
                dataGridView1.Columns[2].DataPropertyName = dt.Columns[2].ToString();
                dataGridView1.Columns[3].DataPropertyName = dt.Columns[3].ToString();
                dataGridView1.Columns[4].DataPropertyName = dt.Columns[4].ToString();
                dataGridView1.DataSource = dt;
            }
            catch 
            { return; }
        }

        private void textBox7_KeyUp(object sender, KeyEventArgs e)
        {
            dt.Clear();
            try
            {
                comStr = "select ygdm,ygxm,ygbh,ksmc,ygjb from gy_ygdm a,gy_ksdm b where a.ksdm=b.ksdm and ygbh like '" + textBox6.Text.Trim() + "%' and ygxm like '" + textBox7.Text.Trim() + "%' order by YGDM";
                dt.Load(SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, comStr, null));
                dataGridView1.Columns[0].DataPropertyName = dt.Columns[0].ToString();
                dataGridView1.Columns[1].DataPropertyName = dt.Columns[1].ToString();
                dataGridView1.Columns[2].DataPropertyName = dt.Columns[2].ToString();
                dataGridView1.Columns[3].DataPropertyName = dt.Columns[3].ToString();
                dataGridView1.Columns[4].DataPropertyName = dt.Columns[4].ToString();
                dataGridView1.DataSource = dt;
            }
            catch
            { return; }
        }

    }
}
